package com.njwb.day14.dao.impl;

import com.njwb.day14.dao.DbUtil;
import com.njwb.day14.dao.StudentDao;
import com.njwb.day14.dao.pojo.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {


    @Override
    public int addStudent(Student stu) throws SQLException {
        int result =-1 ;
        //构建连接对象
        Connection connection= null ;
        //命令器
        PreparedStatement pstmt= null ;
        try {
            connection = DbUtil.getConnection();
            //3、准备sql
            String sql = "insert into tb_student(sname,age,address) values(?,?,?)";
            //4、创建命令器
            pstmt = connection.prepareStatement(sql);
            //5、设置参数
            pstmt.setString(1,stu.getSname());
            pstmt.setInt(2,stu.getAge());
            pstmt.setString(3,stu.getAddress());
            //6、执行sql语句 executeUpdate该方法主要用做更新操作,返回值表示受影响的行数
            result = pstmt.executeUpdate();
        } finally {
            //6、关闭资源 （倒着关闭）
            DbUtil.closeConnection(null,pstmt,connection);
        }
        return result;
    }

    @Override
    public int updateStudent(Student stu) throws SQLException {
        int result = -1 ;
        //构建连接对象
        Connection connection= null ;
        //命令器
        PreparedStatement pstmt= null ;
        try {
            connection = DbUtil.getConnection();
            //3、创建命令器
            pstmt = connection.prepareStatement("update tb_student set sname= ? ,age=?,address=? where sid=?");
            //4、设置参数
            pstmt.setString(1,stu.getSname());
            pstmt.setInt(2,stu.getAge());
            pstmt.setString(3,stu.getAddress());
            pstmt.setInt(4,stu.getSid());
            //5.执行sql语句
            result = pstmt.executeUpdate();
        } finally {
            DbUtil.closeConnection(null,pstmt,connection);
        }
        return result ;
    }

    @Override
    public int deleteStudentById(int sid) throws SQLException {
        int result = -1 ;
        //构建连接对象
        Connection connection= null ;
        //命令器
        PreparedStatement pstmt= null ;
        try {
            connection = DbUtil.getConnection();
            //3、创建命令器
            pstmt = connection.prepareStatement("delete from tb_student where sid =?");
            pstmt.setInt(1,sid);
            //5.执行sql语句
            result = pstmt.executeUpdate();

        }finally {
            DbUtil.closeConnection(null,pstmt,connection);
        }
        return result ;
    }

    @Override
    public Student findStudentById(int sid) throws SQLException {
        Student student= null ;
        //构建连接对象
        Connection connection= null ;
        //命令器
        PreparedStatement pstmt= null ;
        //结果集
        ResultSet rs = null ;
        try {
            connection = DbUtil.getConnection();
            //3、创建命令器
            pstmt = connection.prepareStatement("select sid,sname,age,address from tb_student where sid =?");
            pstmt.setInt(1,sid);
            //5.执行sql语句,返回结果集对象，并读取结果集
            rs = pstmt.executeQuery();
            //读取结果集
            if(rs.next()) {
                student = new Student();
                student.setSid(rs.getInt("sid"));
                student.setSname(rs.getString("sname"));
                student.setAge( rs.getInt("age"));
                student.setAddress(rs.getString("address"));
            }
        }finally {
            DbUtil.closeConnection(rs,pstmt,connection);
        }
        return student ;
    }

    @Override
    public List<Student> queryAllStudents() throws SQLException {
        List<Student> list = new ArrayList<>();
        //构建连接对象
        Connection connection= null ;
        //命令器
        PreparedStatement pstmt= null ;
        //结果集
        ResultSet rs = null ;
        try {
            connection = DbUtil.getConnection();
            //3、创建命令器
            pstmt = connection.prepareStatement("select sid,sname,age,address from tb_student");
            //5.执行sql语句,返回结果集对象，并读取结果集
            rs = pstmt.executeQuery();
            //读取结果集
            while(rs.next()) {
                Student student = new Student();
                student.setSid(rs.getInt("sid"));
                student.setSname(rs.getString("sname"));
                student.setAge( rs.getInt("age"));
                student.setAddress(rs.getString("address"));
                list.add(student);
            }
        }finally {
            DbUtil.closeConnection(rs,pstmt,connection);
        }
        return list;
    }

    @Override
    public boolean logn(String name, String pass) throws SQLException {
        Student student= null ;
        //构建连接对象
        Connection connection= null ;
        //命令器
        PreparedStatement pstmt= null ;
        //结果集
        ResultSet rs = null ;
        try {
            connection = DbUtil.getConnection();
            //3、创建命令器
            pstmt = connection.prepareStatement("select * from users where name = ? and pass = ?");
            pstmt.setString(1,name);
            pstmt.setString(2,pass);
            //5.执行sql语句,返回结果集对象，并读取结果集
            rs = pstmt.executeQuery();
            //读取结果集
            if (rs.next()) {
                return true;
            }

        }finally {
            DbUtil.closeConnection(rs,pstmt,connection);
        }
        return false ;
    }
    @Override
    public Student findStudentByName(String name) throws SQLException {
        Student student= null ;
        //构建连接对象
        Connection connection= null ;
        //命令器
        PreparedStatement pstmt= null ;
        //结果集
        ResultSet rs = null ;
        try {
            connection = DbUtil.getConnection();
            //3、创建命令器
            pstmt = connection.prepareStatement("select sid,sname,age,address from tb_student where sname =?");
            pstmt.setString(1,name);
            //5.执行sql语句,返回结果集对象，并读取结果集
            rs = pstmt.executeQuery();
            //读取结果集
            if(rs.next()) {
                student = new Student();
                student.setSid(rs.getInt("sid"));
                student.setSname(rs.getString("sname"));
                student.setAge( rs.getInt("age"));
                student.setAddress(rs.getString("address"));
            }
        }finally {
            DbUtil.closeConnection(rs,pstmt,connection);
        }
        return student ;
    }
}
